<?php
namespace app\company\controller;

use think\Db;
use app\company\logic\Export;
use PhpOffice\PhpSpreadsheet\IOFactory;
use app\common\lib\SendIntegralNew;
class Reportset extends Base {

    private $where = [];

    public function _initialize(){
        parent::_initialize();
        $this->where = ['com_id'=>$this->com_id];
    }

    /**
     * 自定义分组
     */
    public function index(){
        $list = db('com_statistics_group')->where(['com_id'=>$this->com_id])->order('id desc')->paginate('',false,page_param());
        $this->assign('list', $list);
        return $this->fetch();
    }

    public function index_add(){
        if(request()->isPost()){
            return $this->index_stor();
        }
        $this->assign('titleTypeName', '添加');
        return $this->fetch('index_edit');
    }

    public function index_edit(){
        if(request()->isPost()){
            return $this->index_stor();
        }
        $info = db('com_statistics_group')->where(['id'=>input('id'), 'com_id'=>$this->com_id])->find();
        $this->assign('titleTypeName', '编辑');
        $this->assign('info', $info);
        return $this->fetch('index_edit');
    }

    private function index_stor(){
        $data = input();
        $result = $this->validate(
            $data,
            [
                'name|分组名称'  => 'require|max:10',
                'desc|备注'   => 'max:255'
            ]);
        if(true !== $result){
            return $this->ajaxReturn(1, $result);
        }
        DB::startTrans();
        try{
            if($data['id']){
                $info = db('com_statistics_group')->where(['id'=>$data['id'], 'com_id'=>$this->com_id])->find();
                if(!$info){
                    return $this->ajaxReturn(1, '信息有误');
                }
                db('com_statistics_group')->where(['id'=>$data['id']])->update($data);
            }else{
                //新增的同时新增自定义报表
                $data['com_id'] = $this->com_id;
                $id = db('com_statistics_group')->insertGetId($data);
                //添加自定义报表
                $data_state = [
                    'com_id'=>$this->com_id,
                    'name'=>$data['name'],
                    'type'=>0,
                    'is_join'=>1,
                    'desc'=>$data['desc'],
                    'create_time'=>time(),
                ];
                $state_id = M('com_statistics_statement')->insertGetId($data_state);
                //添加关联表
                $data_state_relation = [
                    'com_id'=>$this->com_id,
                    'statement_id'=>$state_id,
                    'group_id'=>$id,
                ];
                M('com_statement_relation_group')->insert($data_state_relation);
            }
            DB::commit();
            return $this->ajaxReturn(0,"成功", url(CONTROLLER_NAME.'/index'));
        }catch (\Exception $e){
            DB::rollback();
            return $this->ajaxReturn(1,$e->getMessage());
        }
    }

    //分组删除
    public function index_del(){
        $id = input('id');
        $info = db('com_statistics_group')->where(['id'=>$id, 'com_id'=>$this->com_id])->find();
        if(!$info){
            return $this->ajaxReturn(1, '信息有误');
        }
        $u_info = db('com_statistics_group_relation_users')->where(['group_id'=>$id])->find();
        if($u_info){
            return $this->ajaxReturn(1, '删除失败 分组下已有人员');
        }
        //检查报表关联分组表
        $info = db('com_statement_relation_group')->where('group_id', $id)->find();
        if($info){
            return $this->ajaxReturn(1, '删除失败 分组下已有自定义报表');
        }
        db('com_statistics_group')->where(['id'=>$id])->delete();
        return $this->ajaxReturn(0,"成功", url(CONTROLLER_NAME.'/index'));
    }

    //分组人员列表
    public function index_staff(){
        $id = input('id');//分组id
        $com_department = db('com_department')->where(['com_id'=>$this->com_id])->column('id, name');//企业部门
        $where = ['csgru.com_id'=>$this->com_id, 'csgru.group_id'=>$id];
        input('name') && $where['u.name'] = ['like', '%'.input('name').'%'];
        $list = db('com_statistics_group_relation_users')->alias('csgru')
            ->join('users u', 'csgru.user_id=u.id', 'LEFT')->field('csgru.id, csgru.is_participation, u.name, u.department_id')
            ->where($where)->order('csgru.id desc')->paginate('',false,page_param());
        $assign = [
            'list'=>$list,
            'com_department'=>$com_department
        ];
        $this->assign($assign);
        return $this->fetch();
    }
    //更改人员列表是否参与排名
    public function index_staff_update(){
        if(request()->isPost()){
            $data = input();
            $info = db('com_statistics_group_relation_users')
                ->where(['id'=>$data['id'], 'com_id'=>$this->com_id])->find();
            if(!$info){
                return $this->ajaxReturn(1, "信息有误");
            }
            if($data['is_participation'] == 0){
                $data['is_participation'] = 1;
            }else{
                $data['is_participation'] = 0;
            }
            $res = db('com_statistics_group_relation_users')
                ->where(['id'=>$data['id']])->update(['is_participation'=>$data['is_participation']]);
            if($res){
               return $this->ajaxReturn(0, "修改成功");
            }else{
               return $this->ajaxReturn(1, "修改失败");
            }
        }
    }
    //分组人员列表
    public function index_select(){
        $id = input('id');//分组id
        $where = ['com_id'=>$this->com_id, 'group_id'=>$id];
        $list = db('com_statistics_group_relation_users')->where($where)->order('id desc')->paginate('',false,page_param());
        $this->assign('list', $list);
        return $this->fetch();
    }
    //人员弹窗选择
    public function index_select_lay(){
        //post请求为切换部门
        if(request()->isPost()){
            $com_department_id = input('com_department_id');
            $where = [];
            $com_department_id && $where['department_id'] = $com_department_id;
            $where['com_id'] = $this->com_id;
            $where['role_id'] = ['neq',''];
            $list = db('users')->where($where)->field('id, name')->select();
            return $this->ajaxReturn(0,"", '', $list);
        }else{
            $com_department = db('com_department')->where($this->where)->select();
            $com_department = toTree($com_department);
            $com_department = organization_tree($com_department);
            $assign = [
                'com_department'=>$com_department, //部门列表
                'id'=>input('id') //分组id
            ];
            $this->assign($assign);
            return $this->fetch();
        }
    }
    //人员弹窗选择提交
    public function index_select_lay_sub(){
        if(request()->isAjax()){
            $data = input();
            // var_dump($data);die; 
            $user_ids = db('users')->where(['id'=>['in', $data['user_id']], 'com_id'=>$this->com_id])->column('id');
            if(!$user_ids){
                return $this->ajaxReturn(1, '用户信息有误');
            }
            $com_statistics_group = db('com_statistics_group')->where(['id'=>$data['id'], 'com_id'=>$this->com_id])->value('id');
            if(!$com_statistics_group){
                return $this->ajaxReturn(1, '分组信息有误');
            }
            $old_user_ids = db('com_statistics_group_relation_users')->where(['group_id'=>$data['id']])->column('user_id');
            foreach($user_ids as $k=>$v){
                if(in_array($v, $old_user_ids)){
                    unset($user_ids[$k]);
                }
            }
            if($user_ids){
                $insert_data = [];
                foreach($user_ids as $k=>$v){
                    $insert_data[] = [
                        'com_id'=>$this->com_id,
                        'group_id'=>$data['id'],
                        'user_id'=>$v
                    ];
                }
                db('com_statistics_group_relation_users')->insertAll($insert_data);
            }
            return $this->ajaxReturn(0, "添加成功");
        }
    }
    //分组人员删除
    public function index_staff_del(){
        $ids = input('id');
        $id = input('id_a');
        db('com_statistics_group_relation_users')->where(['id'=>['in', $ids], 'com_id'=>$this->com_id])->delete();
        return $this->ajaxReturn(0, "成功", url('reportset/index_staff',['id'=>$id]));
    }
    //下载导入模板
    public function down_exl(){
        $file_name = ROOT_PATH.'public/static/group-template.xlsx';
        header('Content-Description: File Transfer');
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename="group-template.xlsx"');
        header('Expires: 0');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        header('Content-Length: ' . filesize($file_name));
        readfile($file_name);
        exit;
    }
    //上传文件导入人员
    public function index_staff_import_1(){
        require EXTEND_PATH.'PHPExcel/PHPExcel.php';
        require EXTEND_PATH.'PHPExcel/PHPExcel/IOFactory.php';
        if($_FILES['file']['error'] != 0){
            return $this->ajaxReturn(1, "文件上传有误");
        }
        //文件是否大于2M
        if($_FILES['file']['size'] > 102400*2){
            return $this->ajaxReturn(1, "文件最大2M");
        }
        $r = action('file/controller_up');
        if($r['err'] != 0){
            return $this->ajaxReturn(1, "文件保存出错");
        }
        $path = ROOT_PATH.'public'.$r['data']['saveName'];
        $type = 'Excel2007';//设置为Excel5代表支持2003或以下版本，Excel2007代表2007版
        $xlsReader = \PHPExcel_IOFactory::createReader($type);
        $xlsReader->setReadDataOnly(true);
        $xlsReader->setLoadSheetsOnly(true);
        $Sheets = $xlsReader->load($path);
        $dataArray = $Sheets->getSheet(0)->toArray();
        unlink($path);
        $data = [];
        foreach($dataArray as $k=>$v){
            $group_name = trim($v[0]);
            $job_number = trim($v[1]);
            $user_name = trim($v[2]);
            if($k > 1 && $group_name && $job_number && $user_name){
                $data[] = [
                    'group_name'=>$group_name, //分组名称
                    'job_number'=>$job_number, //工号
                    'user_name'=>$user_name   //用户姓名
                ];
            }
        }
        if(!$data){
            return $this->ajaxReturn(1, "导入用户不能为空");
        }
        $com_statistics_group = db('com_statistics_group')->where($this->com_id)->column('name, id'); //部门列表
        $data_group_name = array_unique(array_column($data, 'group_name'));
        //检查表格中的部门是否有误
        foreach($data_group_name as $k=>$v){
            if(!array_key_exists($v, $com_statistics_group)){
                return $this->ajaxReturn(1, "导入失败 分组名称 {$v} 不存在");
            }
        }
        //检查人员信息是否有误
        $users = db('users')->where(['com_id'=>$this->com_id, 'type'=>1])->column('name, id, job_number');//企业已有人员
        foreach($data as $k=>$v){
            if(array_key_exists($v['user_name'], $users) && $v['job_number'] == $users[$v['user_name']]['job_number']){
                $data[$k]['user_id'] = $users[$v['user_name']]['id'];
                $data[$k]['group_id'] = $com_statistics_group[$v['group_name']];
                $data[$k]['com_id'] = $this->com_id;
                unset($data[$k]['group_name'], $data[$k]['job_number'], $data[$k]['user_name']);
            }else{
                return $this->ajaxReturn(1, "导入失败 人员 {$v['user_name']} 信息有误或已离职");
            }
        }
        $group_relation_users = db('com_statistics_group_relation_users')->where('com_id', $this->com_id)
            ->field('group_id, user_id')->select();
        //过滤已存在的分组关联用户
        $data = array_unique($data, SORT_REGULAR);
        foreach($data as $k=>$v){
            foreach($group_relation_users as $k2=>$v2){
                if($v2['group_id'] == $v['group_id'] && $v2['user_id'] == $v['user_id']){
                    unset($data[$k]);
                }
            }
        }
        db('com_statistics_group_relation_users')->insertAll($data);
        return $this->ajaxReturn(0, "导入成功");
    }
    //上传文件导入人员@stone
    public function index_staff_import(){
        if($_FILES['file']['error'] != 0){
            return $this->ajaxReturn(1, "文件上传有误");
        }
        //文件是否大于2M
        if($_FILES['file']['size'] > 102400*5){
            return $this->ajaxReturn(1, "文件最大5M");
        }
        $r = action('file/controller_up');
        if($r['err'] != 0){
            return $this->ajaxReturn(1, "文件保存出错");
        }
        $path = ROOT_PATH.'public'.$r['data']['saveName'];
        $type = 'Xlsx';//设置为Xls代表支持2003或以下版本，Xlsx代表2007版
        $xlsReader = IOFactory::createReader($type);
        $xlsReader->setReadDataOnly(true);
        $Sheets = $xlsReader->load($path);
        $dataArray = $Sheets->getSheet(0)->toArray();
        unlink($path);
        unset($dataArray[0]);       //删除第一行，从第二行开始读取
        unset($dataArray[1]);       //删除第二行，从第三行开始读取
        //分组
        $group_names = M('com_statistics_group')->where(['com_id'=>$this->com_id])->column('name');
        $group_ids = M('com_statistics_group')->where(['com_id'=>$this->com_id])->column('id','name');
        $job_number_ids = M('users')->where(['com_id'=>$this->com_id])->column('job_number');
        $user_ids = M('users')->where(['com_id'=>$this->com_id])->column('id','job_number');
        $com_statistics_group_relation_users = M('com_statistics_group_relation_users')->where('com_id',$this->com_id)->field('group_id,user_id')->select();
        $group_ralation_array = [];
        foreach ($com_statistics_group_relation_users as $value){
            $group_ralation_array[] = $value['group_id'].'-'.$value['user_id'];
        }
//        var_dump($user_ids);die;
        $err_msg = '';
        $data = [];
        foreach ($dataArray as $key=>$row){
            $index = $key + 1;
            if(!empty($row[0])) {
                //字段验证
                if (empty($row[0])||empty($row[1])||empty($row[2])){
                    $err_msg .= '第'.$index.'行数据缺失';
                }
                //查询分组名称是否存在
                if(!in_array($row[0],$group_names)){
                    $err_msg .= '第'.$index.'行数据分组名称不存在';
                }
                //查询用户是否存在
                if(!in_array($row[1],$job_number_ids)){
                    $err_msg .= '第'.$index.'行数据工号不存在';
                }
                //判断是否重复的数据
                $group_id = $group_ids[$row[0]]??0;
                $user_id = $user_ids[$row[1]]??0;
                $group_ralation = $group_id.'-'.$user_id;
                if(in_array($group_ralation,$group_ralation_array)){
                    $err_msg .= '第'.$index.'行数据重复';
                }
                array_push($group_ralation_array,$group_ralation);
                $data[] = [
                    'com_id'=>$this->com_id,
                    'group_id'=>$group_id,
                    'user_id'=>$user_id
                ];
            }
        }
        if(!empty($err_msg)){
            return $this->ajaxReturn(1,$err_msg);
        }else{
            DB::startTrans();
            try{
                if(!empty($data)){
                    M('com_statistics_group_relation_users')->insertAll($data);
                }
                DB::commit();
                return $this->ajaxReturn(0,'导入成功');
            }catch (\Exception $e){
                DB::rollback();
                return $this->ajaxReturn(1,$e->getMessage());
            }
        }
    }

    /**
     * 自定义报表
     */
    public function custom_report(){
        $list = db('com_statistics_statement')->where(['com_id'=>$this->com_id])->order('id desc')->paginate('',false,page_param());
        $this->assign('list', $list);
        return $this->fetch();
    }

    public function custom_report_add(){
        if(request()->isPost()){
            return $this->custom_report_stor();
        }
        $this->custom_report_assign();
        $this->assign('titleTypeName', '添加');
        return $this->fetch('custom_report_edit');
    }

    public function custom_report_edit(){
        if(request()->isPost()){
            return $this->custom_report_stor();
        }
        $this->custom_report_assign();
        $info = db('com_statistics_statement')->where(['id'=>input('id'), 'com_id'=>$this->com_id])->find();
        $statement_relation_group = db('com_statement_relation_group')
            ->where(['statement_id'=>input('id'), 'com_id'=>$this->com_id])->column('group_id');
        $this->assign('titleTypeName', '编辑');
        $this->assign('info', $info);
        $this->assign('statement_relation_group', $statement_relation_group);//已选的关联分组
        return $this->fetch('custom_report_edit');
    }
    //自定义报表通用信息模板赋值
    private function custom_report_assign(){
        $statistics_group = db('com_statistics_group')->where(['com_id'=>$this->com_id])->field('id, name')->select();
        $this->assign('statistics_group', $statistics_group);
    }

    private function custom_report_stor(){
        $data = input();
        $result = $this->validate(
            $data,
            [
                'name|报表名称'  => 'require|max:50'
            ]);
        if(true !== $result){
            return $this->ajaxReturn(1, $result);
        }
        if(!isset($data['group_id']) || !$data['group_id']){
            return $this->ajaxReturn(1, '未选择包含分组');
        }
        $group_data = [];//报表关联分组表插入数据
        foreach($data['group_id'] as $k=>$v){
            $group_data[] = [
                'com_id'=>$this->com_id,
                'statement_id'=>$data['id'],
                'group_id'=>$v
            ];
        }
        Db::startTrans();
        try{
            if($data['id']){
                $info = db('com_statistics_statement')->where(['id'=>$data['id'], 'com_id'=>$this->com_id])->find();
                if(!$info){
                    return $this->ajaxReturn(1, '信息有误');
                }
                db('com_statistics_statement')->where(['id'=>$data['id']])->update($data);
                db('com_statement_relation_group')->where('statement_id', $data['id'])->delete();
            }else{
                $data['com_id'] = $this->com_id;
                $data['create_time'] = time();
                $statement_id = db('com_statistics_statement')->insertGetId($data);
                foreach($group_data as $k=>$v){
                    $group_data[$k]['statement_id'] = $statement_id;
                }
            }
            db('com_statement_relation_group')->insertAll($group_data);
            Db::commit();
        } catch (\Exception $e) {
            Db::rollback();
            return $this->ajaxReturn(1,"操作失败");
        }
        return $this->ajaxReturn(0,"成功", url(CONTROLLER_NAME.'/custom_report'));
    }

    //自定报表删除
    public function custom_report_del(){
        $id = input('id');

        Db::startTrans();
        try{
            db('com_statement_relation_group')->where(['statement_id'=>$id, 'com_id'=>$this->com_id])->delete();
            db('com_statistics_statement')->where(['id'=>$id, 'com_id'=>$this->com_id])->delete();
            Db::commit();
        } catch (\Exception $e) {
            Db::rollback();
            return $this->ajaxReturn(1,"删除失败");
        }
        return $this->ajaxReturn(0,"成功", url('reportset/custom_report'));
    }

    //预览排名 author:zj
    public function custom_report_ranking(){
        $id = input('id', 0, 'intval');//自定义统计报表id
        $act = input('act', 'y');//排名类型 默认月份排名
        $where = [];
        $info = db('com_statistics_statement')->where(['id'=>$id, 'com_id'=>$this->com_id])->find();
        if(!$info){
            return $this->ajaxReturn(1, '查找的报表不存在');
        }
        //获取报表关联的组
        $group_ids = db('com_statement_relation_group')->where(['statement_id'=>$id, 'com_id'=>$this->com_id])->column('group_id');
        $groupNames = db('com_statistics_group')->where('id', 'in', $group_ids)->column('id,name');
        //获取组的成员
        $userInfos = db('com_statistics_group_relation_users a')
            ->join('users b', 'a.user_id = b.id', 'left')
            ->join('com_department c', 'c.id = b.department_id', 'left')
            ->where(['a.group_id'=>['in', $group_ids], 'a.com_id'=>$this->com_id])
            ->where('b.is_sort', 1)         //是否参与排名  0否  1是',
            ->where('a.is_participation', 0)         //是否参与排名  0是  1否',
            ->group('b.id')
            ->field('b.*,b.integral_b integral_aa, c.name as d_name, a.group_id as group_id')
            ->select();
        $tmpUserIds = [];
        foreach ($userInfos as $key=>$value){
            $tmpUserIds[] = $value['id'];
        }
        //查询时间
        $s_time = input('s_time', date('Y-m'));
        $e_time = input('e_time', date('Y-m'));
        if($act == 'y'){
            $s_time_int = strtotime($s_time.'-01');
            $e_time_int = strtotime('+1 month',$s_time_int)-1;
            $where['create_time'] = ['between',[$s_time_int,$e_time_int]];
        }elseif($act == 'j'){
//            $s_time_int = strtotime($s_time.'-01');
//            $e_time_int = strtotime('+1 month',strtotime($e_time.'-01'))-1;
            $s_time = input('s_time', date('Y-m-1'));
            $e_time = input('e_time', date('Y-m-d'));
            $s_time_int = strtotime($s_time);
            $e_time_int = strtotime($e_time)+3600*24-1;
            $where['create_time'] = ['between',[$s_time_int,$e_time_int]];
        }
        $where['com_id'] = $this->com_id;
        //表类型
        if($info['type'] == 0) {
            //获取每个用户对应的积分
            //是否加入基础分和工龄分
            foreach ($userInfos as $key => $v) {
                if($act != 'l') {
                    if ($info['is_join'] == 0) {
                        $userInfos[$key]['integral_b'] = SendIntegralNew::statistic_integral($s_time_int,$e_time_int,$v['id'],1);
                    }else{
                        $userInfos[$key]['integral_b'] = SendIntegralNew::statistic_integral($s_time_int,$e_time_int,$v['id'],0);
                    }
                }else{
                    $userInfos[$key]['integral_b'] = SendIntegralNew::statistic_integral(strtotime('2019-01-01'),time(),$v['id'],1);
//                    var_dump($userInfos[$key]['integral_b']);
//                    $userInfos[$key]['integral_b'] = $v['base_integral'] + $v['working_age_integral']+$v['integral_aa'];
                }
            }
        }else{
            //查询分组数据
            $group_list = M('com_statement_relation_group a')
                ->join('com_statistics_group b','a.group_id=b.id')
                ->where(['a.statement_id'=>$id, 'a.com_id'=>$this->com_id])
                ->field('a.group_id,b.name')
                ->select();
            foreach ($group_list as $key=>$value){
                $ids = M('users a')
                    ->join('com_statistics_group_relation_users b','b.user_id=a.id')
                    ->where(['a.com_id'=>$this->com_id,'b.group_id'=>$value['group_id'],'a.is_sort'=>1,'a.type'=>1,'b.is_participation'=>0])->column('a.id');
                $count = count($ids);
                $where['user_id'] = ['in',$ids];
                $sum = 0;
                if($act != 'l') {
                    foreach ($ids as $key1 => $value1) {
                        $sum += SendIntegralNew::statistic_integral($s_time_int, $e_time_int, $value1, 0);
                    }
                }else{
                    foreach ($ids as $key1 => $value1) {
                        $sum += SendIntegralNew::statistic_integral(strtotime('2019-01-01'), time(), $value1, 0);
                    }
                }
//                var_dump($sum);die;
//                $sum = M('com_integral_log')->where($where)->sum('integral_b');
                //是否加入基础分和工龄分
                if($info['is_join'] == 0){
                    $users_integral_total = M('users')->where('id','in',$ids)->field('sum(base_integral) base_integral,sum(working_age_integral) working_age_integral')->find();
                    $sum = $sum + $users_integral_total['base_integral'] + $users_integral_total['working_age_integral'];
                }
                if($count == 0 || $sum == 0){
                    $avg = 0;
                }else{
                    $avg = ceil($sum/$count);
                }
                $group_list[$key]['integral_b'] = $avg;
            }
            $userInfos  = $group_list;
        }
        if (!empty($userInfos)) {
            $tmp = array_column($userInfos, 'integral_b');
            array_multisort($tmp, SORT_DESC, $userInfos);   //二维数组排序
        }
        //排序
        $i = 1;
        foreach ($userInfos as $key => $value) {
            $userInfos[$key]['sort'] = $i;
            if (isset($userInfos[$key + 1]) && $userInfos[$key + 1]['integral_b'] == $value['integral_b']) {

            } else {
                $i++;
            }

        }
        if(input('export',0) == 1){
            $this->custom_report_ranking_explode($userInfos,$info['type']);
        }
        $this->assign('groupNames',$groupNames);
        $this->assign('list',$userInfos);

        $data = [
            'type'=>$info['type'],
            'act'=>$act,
            's_time'=>$s_time,
            'e_time'=>$e_time
        ];
        $this->assign($data);

        return $this->fetch();
    }

    //预览排名
    public function custom_report_ranking_bak(){
        $id = input('id', 0, 'intval');//自定义统计报表id
        $act = input('act', 'y');//排名类型 默认月份排名
        $where = ['com_id'=>$this->com_id];//积分统计条件
        $field = ['user_id'];//统计字段

        $info = db('com_statistics_statement')->where(['id'=>$id, 'com_id'=>$this->com_id])->find();
        if(!$info){
            return $this->ajaxReturn(1, '查找的报表不存在');
        }

        //查询时间
        $s_time = input('s_time', date('Y-m'));
        $e_time = input('e_time', date('Y-m'));
        if($act == 'y'){
            $where_time = explode('-', $s_time);
            $where['year'] = $where_time[0];
            $where['month'] = $where_time[1];
        }elseif($act == 'j'){
            $s_time = input('s_time', date('Y-01'));
            $s_time_int = strtotime($s_time);
            $e_time_int = strtotime($e_time) + date("t",strtotime($e_time)) * 86400;
            if($s_time_int > $e_time_int){
                $s_time_int = $e_time_int;
            }
            $where['create_time'] = [['>=', $s_time_int], ['<=', $e_time_int], 'and'];
        }

        //0查看全部分组 1所在分组
        if($info['scope_type'] == 0){
            $group_ids = db('com_statistics_group')->where('com_id', $this->com_id)->column('id');
        }elseif($info['scope_type'] == 1){
            $group_ids = db('com_statement_relation_group')->where('statement_id', $id)->column('group_id');
        }
        //0积分排名  1平均分排名
        if($info['type'] == 0){
            $field['sum(integral_b)'] = 'integral_b';
        }elseif($info['type'] == 1){
            $field['avg(integral_b)'] = 'integral_b';
        }
        //要统计的用户
        $user_ids = db('com_statistics_group_relation_users')->where([
            'group_id'=>['in', $group_ids]
        ])->column('user_id');
        $user_ids = array_unique($user_ids);
        $user_list = db('users')->alias('u')
            ->join('com_department cd', 'cd.id=u.department_id', 'LEFT')
            ->where(['u.id'=>['in', $user_ids], 'u.is_sort'=>1])->column('u.id, u.name, u.job_number, u.entry_time, cd.name as d_name');
        $user_ids = array_column($user_list, 'id');
        //积分记录统计
        $integral_log = db('com_integral_log')->where($where)->group('user_id')->column($field);
        foreach($user_list as $k=>$v){
            if(array_key_exists($v['id'], $integral_log)){
                $user_list[$k]['integral'] = $integral_log[$v['id']];
            }else{
                $user_list[$k]['integral'] = 0;
            }
        }
        //是否加入基础分和工龄分   0是  1否
        if($info['is_join'] == 0 && $act == 'l'){
            $conf = db('com_config')->where(['com_id'=>$this->com_id])
                ->field('start_integral, working_age_type, working_age_integral, working_age_end')->find();
            if($conf){
                $now_time = time(); //工龄分截止时间
                if( ($conf['working_age_end'] && $conf['working_age_end'] > $now_time) || !$conf['working_age_end'] ){
                    $conf['working_age_end'] = $now_time;
                }
                $conf['working_age_end'] = date('Y-m-d', $conf['working_age_end']);
                //工龄结算类型 0按月结算  1按天结算
                $end = new \DateTime($conf['working_age_end']);
                if($conf['working_age_type'] == 0){
                    $interval = \DateInterval::createFromDateString('1 month');
                }elseif($conf['working_age_type'] == 1){
                    $interval = \DateInterval::createFromDateString('1 day');
                }
                foreach($integral_log as $k=>$v){
                    if(!isset($user_list[$k])){
                        continue;
                    }
                    $start = new \DateTime(date('Y-m-d', $user_list[$k]['entry_time']));
                    $period = new \DatePeriod($start, $interval, $end);
                    $count = 0;
                    foreach($period as $k2=>$v2){
                        $count++;
                    }
                    $user_list[$k]['integral'] += $conf['working_age_integral'] * $count + $conf['start_integral'];
                }
            }
        }
        array_multisort(array_column($user_list,'integral'),SORT_DESC,$user_list);
        $level = 1;//名次
        $level_int = 0;//用于计算名次递增的分值
        foreach($user_list as $k=>$v){
            if($k == 0){
                $level_int = $v['integral'];
            }elseif($v['integral'] < $level_int){
                $level_int = $v['integral'];
                $level++;
            }
            $user_list[$k]['level'] = $level;
        }
        if(input('export')){
            $this->custom_report_ranking_explode($user_list);
        }
        $data = [
            'user_list'=>$user_list,
            'act'=>$act,
            's_time'=>$s_time,
            'e_time'=>$e_time
        ];
        $this->assign($data);
        return $this->fetch();
    }
    //预览排名导出
    public function custom_report_ranking_explode_1($list){
        $strTable = '<table width="500" border="1">';
        $strTable .= '<tr>';
        $strTable .= '<td style="text-align:center;font-size:12px;width:*">排名</td>';
        $strTable .= '<td style="text-align:center;font-size:12px;width:*">姓名</td>';
        $strTable .= '<td style="text-align:center;font-size:12px;width:120px;">工号</td>';
        $strTable .= '<td style="text-align:center;font-size:12px;" width="*">部门</td>';
        $strTable .= '<td style="text-align:center;font-size:12px;" width="*">分值</td>';
        $strTable .= '</tr>';
        if (!empty($list)) {
            foreach ($list as $key => $val) {
                $strTable .= '<tr>';
                $strTable .= '<td style="text-align:left;font-size:12px;">' . $val['level'] . '</td>';
                $strTable .= '<td style="text-align:left;font-size:12px;">' . $val['name'] . '</td>';
                $strTable .= '<td style="text-align:left;font-size:12px;">' . $val['job_number'] . '</td>';
                $strTable .= '<td style="text-align:left;font-size:12px;">' . $val['d_name'] . '</td>';
                $strTable .= '<td style="text-align:left;font-size:12px;">' . floor($val['integral']) . '</td>';
                $strTable .= '</tr>';
            }
        }
        $strTable .= '</table>';
        unset($list);
        $name = date('Y-m-d');
        downloadExcel($strTable, $name);
        exit();
    }
    //预览排名导出@stone
    public function custom_report_ranking_explode($list,$type=0){
        if($type == 0) {
            $title = [
                '排名',
                '姓名',
                '工号',
                '部门',
                '分值',
            ];
            $list_data = [];
            foreach ($list as $key => $val) {
                $list_data[] = [
                    $val['sort'],
                    $val['name'],
                    $val['job_number'],
                    $val['d_name'],
                    $val['integral_b'],
                ];
            }
        }else{
            $title = [
                '排名',
                '分组',
                '平均分',
            ];
            $list_data = [];
            foreach ($list as $key => $val) {
                $list_data[] = [
                    $val['sort'],
                    $val['name'],
                    $val['integral_b'],
                ];
            }
        }
        $name = '预览排名报表';
        //导出excel
        Export::excel($title,$list_data,$name,20,['C']);
    }

    /**
     * 我的排名报表
     */
    public function my_report(){
        $u_id = session("user_com.id");
        $is_sort = db('users')->where('id', $u_id)->value('is_sort');
        if($is_sort != 1){
            $this->error('用户未参与统计排名');
        }
        //获取与用户相关的自定义统计报表id数组
        $statement_ids = db('com_statistics_group_relation_users')->alias('csgru')
            ->join('com_statement_relation_group csrg', 'csrg.group_id=csgru.group_id', 'LEFT')
            ->where(['csgru.user_id'=>$u_id])
            ->column('csrg.statement_id');
        if(!$statement_ids){
            $this->error('用户统计排名不存在');
        }
        $list = db('com_statistics_statement')->where(['id'=>['in', $statement_ids]])->order('id desc')->paginate('',false,page_param());
        $this->assign('list', $list);
        return $this->fetch();
    }

    /**
     * 组织机构排名列表
     */
    public function integral_rank(){
        $list = db('com_department')->where($this->where)->select();
        $list = toTree($list);
        $list = organization_tree($list);
        $this->assign('list', $list);
        return $this->fetch();
    }
    //组织机构排名人员列表
    public function integral_rank_ranking(){
        $id = input('id', 0, "intval");//部门id
        $child_show = input('child_show', 0, 'intval');//是否显示子级部门
        $act = input('act', 'y');//排名类型
        $where = ['com_id'=>$this->com_id];//积分统计条件
        //查询时间
        $s_time = input('s_time', date('Y-m'));
        $e_time = input('e_time', date('Y-m'));
        if($act == 'y'){
            $s_time_int = strtotime($s_time.'-01');
            $e_time_int = strtotime('+1 month',$s_time_int)-1;
        }elseif($act == 'j'){
            $s_time_int = strtotime($s_time.'-01');
            $e_time_int = strtotime('+1 month',strtotime($e_time.'-01'))-1;
        }
        //部门列表
        $department_list = db('com_department')->where(['com_id'=>$this->com_id])->column('id, pid, name');
        if($id && !array_key_exists($id, $department_list)){
            return $this->ajaxReturn(1, '部门有误');
        }
        $department_ids = [intval($id)];//要查找的部门id
        if($child_show){
            $department_ids = department_child($department_ids, $this->com_id);
        }
        //要查询的部门下的用户列表
        $user_list = db('users')->where(['department_id'=>['in', $department_ids], 'is_sort'=>1, 'type'=>1])->column('id, name, job_number, department_id, integral_b, base_integral, working_age_integral');
        $user_ids = array_column($user_list, 'id');
        $where['user_id'] = ['in', $user_ids];
        //累计排名加入基础分和工龄分
        if($act == 'l'){
            foreach($user_list as $k=>$v){
//                $user_list[$k]['integral'] = $v['integral_b'] + $v['base_integral'] + $v['working_age_integral'];
                $user_list[$k]['integral'] = SendIntegralNew::statistic_integral(strtotime('2019-01-01'), time(), $v['id'], 1);
            }
        }else{
            foreach($user_list as $k=>$v){
                $user_list[$k]['integral'] = SendIntegralNew::statistic_integral($s_time_int,$e_time_int,$v['id'],0);
            }
        }
        array_multisort(array_column($user_list,'integral'),SORT_DESC,$user_list);
        $level = 1;//名次
        $level_int = 0;//用于计算名次递增的分值
        foreach($user_list as $k=>$v){
            if($k == 0){
                $level_int = $v['integral'];
            }elseif($v['integral'] < $level_int){
                $level_int = $v['integral'];
                $level++;
            }
            $user_list[$k]['level'] = $level;
        }
        if(input('export')){
            $this->integral_rank_ranking_explode($user_list, $department_list);
        }
        $data = [
            'department_list'=>$department_list,
            'user_list'=>$user_list,
            'act'=>$act,
            's_time'=>$s_time,
            'e_time'=>$e_time
        ];
        $this->assign($data);
        return $this->fetch();
    }
    //组织机构排名导出
    public function integral_rank_ranking_explode_1($list, $department_list){
        $strTable = '<table width="500" border="1">';
        $strTable .= '<tr>';
        $strTable .= '<td style="text-align:center;font-size:12px;width:*">排名</td>';
        $strTable .= '<td style="text-align:center;font-size:12px;width:*">姓名</td>';
        $strTable .= '<td style="text-align:center;font-size:12px;width:120px;">工号</td>';
        $strTable .= '<td style="text-align:center;font-size:12px;" width="*">部门</td>';
        $strTable .= '<td style="text-align:center;font-size:12px;" width="*">分值</td>';
        $strTable .= '</tr>';
        if (!empty($list)) {
            foreach ($list as $key => $val) {
                $strTable .= '<tr>';
                $strTable .= '<td style="text-align:left;font-size:12px;">' . $val['level'] . '</td>';
                $strTable .= '<td style="text-align:left;font-size:12px;">' . $val['name'] . '</td>';
                $strTable .= '<td style="text-align:left;font-size:12px;">' . $val['job_number'] . '</td>';
                $strTable .= '<td style="text-align:left;font-size:12px;">' . $department_list[$val["department_id"]]['name'] . '</td>';
                $strTable .= '<td style="text-align:left;font-size:12px;">' . floor($val['integral']) . '</td>';
                $strTable .= '</tr>';
            }
        }
        $strTable .= '</table>';
        unset($list);
        $name = '组织机构排名报表'.date('Y-m-d H:i');
        downloadExcel($strTable, $name);
        exit();
    }
    //组织机构排名导出@stone
    public function integral_rank_ranking_explode($list, $department_list){
        $title = [
            '排名',
            '姓名',
            '工号',
            '部门',
            '分值',
        ];
        $list_data = [];
        foreach ($list as $key => $val) {
            $department_name = $department_list[$val["department_id"]]['name']??'';
            $list_data[]=[
                $val['level'],
                $val['name'],
                $val['job_number'],
                $department_name,
                $val['integral'],
            ];
        }
        $name = '组织机构排名报表';
        //导出excel
        Export::excel($title,$list_data,$name,20,['C']);
    }

    /**
     * 管理层奖扣报表
     */
    public function manage_report(){
        $list = db('com_department')->where($this->where)->select();
        $list = toTree($list);
        $list = organization_tree($list);
        $this->assign('list', $list);
        return $this->fetch();
    }
    //组织机构排名人员列表
    public function manage_report_ranking(){
        $id = input('id', 0, "intval");//部门id
        $type = input('type', 1, "intval");//查询分类
        $child_show = input('child_show', 0, 'intval');//是否显示子级部门
        $range_time = input('range_time', date('Y-m').' - '.date('Y-m'));//选择的月份范围
        $where = [];//积分统计条件
        //转换为时间戳查找范围
        $range_time_str = explode(' - ', $range_time);
        $s_time_int = strtotime($range_time_str[0]);
        $e_time_int = strtotime($range_time_str[1]) + date("t",strtotime($range_time_str[1])) * 86400;
        $where['a.prize_buckle_time'] = [['>=', $s_time_int], ['<=', $e_time_int], 'and'];
        //部门列表
        $department_list = db('com_department')->where(['com_id'=>$this->com_id])->column('id, pid, name');
        if($id && !array_key_exists($id, $department_list)){
            return $this->ajaxReturn(1, '部门有误');
        }
        $department_ids = [intval($id)];//要查找的部门id
        if($child_show){
            $department_ids = department_child($department_ids, $this->com_id);
        }
        //查询部门下有奖扣权限的用户列表
        $user_list = db('users')->alias('u')
            ->join('com_prize_buckle_relation_users bru', 'bru.user_id=u.id', 'LEFT')
            ->join('com_prize_buckle pb', 'pb.id=bru.prize_buckle_id', 'LEFT')
            ->where(['u.department_id'=>['in', $department_ids], 'u.is_sort'=>1, 'u.type'=>1, 'bru.prize_buckle_id'=>['>', 0]])
            ->column('u.id, u.name, u.job_number, u.department_id, pb.integral_b');
        $user_ids = array_column($user_list, 'id');
        $log_list = [];
        $where['a.first_check_user_id'] = ['in', $user_ids];
        $where['a.status'] = 2;
        $where['c.is_cancellation'] = 0;//未作废
        if($type == 1){
            $column_name = '积分';
            $log_list = M('com_integral_prize_buckle a')
                ->join('com_integral_prize_buckle_relation_event b','b.prize_buckle_id=a.id','right')
                ->join('com_integral_event_relation_users c','c.event_id=b.id','right')
                ->where($where)
                ->group('a.first_check_user_id')
                ->column('a.first_check_user_id,COALESCE(sum(IF(c.integral_b>0,IF(c.piece_rate=0,c.integral_b,c.integral_b*c.piece_rate),0)),0) integral_b_add');
        }elseif ($type == 2){
            $column_name = '积分';
            $log_list = M('com_integral_prize_buckle a')
                ->join('com_integral_prize_buckle_relation_event b','b.prize_buckle_id=a.id','right')
                ->join('com_integral_event_relation_users c','c.event_id=b.id','right')
                ->where($where)
                ->group('a.first_check_user_id')
                ->column('a.first_check_user_id,COALESCE(sum(IF(c.integral_b<0,IF(c.piece_rate=0,c.integral_b,c.integral_b*c.piece_rate),0)),0) integral_b_del');
            foreach($log_list as $k=>$v){
                $log_list[$k] = abs($v);
            }
        }elseif($type == 3){
            $column_name = '奖扣事件数';
            unset($where['c.is_cancellation']);
            $log_list = M('com_integral_prize_buckle a')
                ->join('com_integral_prize_buckle_relation_event b','b.prize_buckle_id=a.id','right')
                ->where($where)
                ->group('a.first_check_user_id')
                ->column('a.first_check_user_id,count(b.id) prize_buckle_num');
        }elseif($type == 4){
            $column_name = '奖扣人次排名';
            $log_list = M('com_integral_prize_buckle a')
                ->join('com_integral_prize_buckle_relation_event b','b.prize_buckle_id=a.id','right')
                ->join('com_integral_event_relation_users c','c.event_id=b.id','right')
                ->where($where)
                ->group('a.first_check_user_id')
                ->column('a.first_check_user_id,COALESCE(count(c.id),0) people_sum');
        }elseif($type == 5){
            $column_name = '奖扣分合计';
            $log_list = M('com_integral_prize_buckle a')
                ->join('com_integral_prize_buckle_relation_event b','b.prize_buckle_id=a.id','right')
                ->join('com_integral_event_relation_users c','c.event_id=b.id','right')
                ->where($where)
                ->group('a.first_check_user_id')
                ->column('a.first_check_user_id,COALESCE(sum(abs(IF(c.piece_rate=0,c.integral_b,c.integral_b*c.piece_rate))),0) integral_b');
        }elseif($type == 6){
            $column_name = '奖扣比例';
            $tmp = M('com_integral_prize_buckle a')
                ->join('com_integral_prize_buckle_relation_event b','b.prize_buckle_id=a.id','right')
                ->join('com_integral_event_relation_users c','c.event_id=b.id','right')
                ->where($where)
                ->group('a.first_check_user_id')
                ->column('a.first_check_user_id,COALESCE(sum(IF(c.integral_b>0,IF(c.piece_rate=0,c.integral_b,c.integral_b*c.piece_rate),0)),0) integral_b_add,COALESCE(sum(IF(c.integral_b<0,IF(c.piece_rate=0,c.integral_b,c.integral_b*c.piece_rate),0)),0) integral_b_del');
            foreach($tmp as $k=>$v){
                if(!$v['integral_b_del'] || !$v['integral_b_add']){
                    continue;
                }
                $log_list[$k] = round(abs($v['integral_b_del'])/$v['integral_b_add'], 4)*100;
            }
        }
        foreach($user_list as $k=>$v){
            if(isset($log_list[$v['id']])){
                $user_list[$k]['integral'] = $log_list[$v['id']];
            }else{
                $integral = 0;
                $user_list[$k]['integral'] = $integral;
            }
        }
        array_multisort(array_column($user_list,'integral'),SORT_DESC,$user_list);
        $level = 1;//名次
        $level_int = 0;//用于计算名次递增的分值
//        var_dump($user_list);die;
        foreach($user_list as $k=>$v){
            if($k == 0){
                $level_int = $v['integral'];
            }elseif($v['integral'] < $level_int){
                $level_int = $v['integral'];
                $level++;
            }
            $user_list[$k]['level'] = $level;
            if($type == 6){
                $user_list[$k]['integral'] = $v['integral'].'%';
            }
        }
        if(input('export')){
            $this->manage_report_explode($user_list, $department_list, $column_name);
        }
        $data = [
            'department_list'=>$department_list,
            'user_list'=>$user_list,
            'column_name'=>$column_name,
            'range_time'=>$range_time
        ];
        $this->assign($data);
        return $this->fetch();
    }
    //组织机构排名导出
    public function manage_report_explode_1($list, $department_list, $column_name){
        $strTable = '<table width="500" border="1">';
        $strTable .= '<tr>';
        $strTable .= '<td style="text-align:center;font-size:12px;width:*">排名</td>';
        $strTable .= '<td style="text-align:center;font-size:12px;width:*">姓名</td>';
        $strTable .= '<td style="text-align:center;font-size:12px;width:120px;">工号</td>';
        $strTable .= '<td style="text-align:center;font-size:12px;" width="*">部门</td>';
        $strTable .= '<td style="text-align:center;font-size:12px;" width="*">'.$column_name.'</td>';
        $strTable .= '<td style="text-align:center;font-size:12px;" width="*">B分权限</td>';
        $strTable .= '</tr>';
        if (!empty($list)) {
            foreach ($list as $key => $val) {
                $strTable .= '<tr>';
                $strTable .= '<td style="text-align:left;font-size:12px;">' . $val['level'] . '</td>';
                $strTable .= '<td style="text-align:left;font-size:12px;">' . $val['name'] . '</td>';
                $strTable .= '<td style="text-align:left;font-size:12px;">' . $val['job_number'] . '</td>';
                $strTable .= '<td style="text-align:left;font-size:12px;">' . $department_list[$val["department_id"]]['name'] . '</td>';
                $strTable .= '<td style="text-align:left;font-size:12px;">' . $val['integral'] . '</td>';
                $strTable .= '<td style="text-align:left;font-size:12px;">' . $val['integral_b'] . '</td>';
                $strTable .= '</tr>';
            }
        }
        $strTable .= '</table>';
        unset($list);
        $name = '管理层奖扣分报表'.date('Y-m-d H:i');
        downloadExcel($strTable, $name);
        exit();
    }
    //组织机构排名导出@stone
    public function manage_report_explode($list, $department_list, $column_name){
        $title = [
            '排名',
            '姓名',
            '工号',
            '部门',
            $column_name,
            'B分权限',
        ];
        $list_data = [];
        foreach ($list as $key => $val) {
            $department_name = $department_list[$val["department_id"]]['name']??'';
            $list_data[]=[
                $val['level'],
                $val['name'],
                $val['job_number'],
                $department_name,
                $val['integral'],
                $val['integral_b'],
            ];
        }
        $name = '管理层奖扣分报表';
        //导出excel
        Export::excel($title,$list_data,$name,20,['C']);

    }
}